package com.example.backend.repository;

import com.example.backend.domain.entity.File;
import com.example.backend.domain.enums.FileStatus;
import com.example.backend.domain.enums.FileType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

public interface FileRepository extends JpaRepository<File, Long>, JpaSpecificationExecutor<File> {
    
    // 基础查询
    Page<File> findByFileType(FileType fileType, Pageable pageable);
    Page<File> findByStatus(FileStatus status, Pageable pageable);
    Page<File> findByUploadedBy(Long uploadedBy, Pageable pageable);
    Page<File> findByTicketId(Long ticketId, Pageable pageable);
    Page<File> findByCommentId(Long commentId, Pageable pageable);
    
    // 复合查询
    Page<File> findByFileTypeAndStatus(FileType fileType, FileStatus status, Pageable pageable);
    Page<File> findByUploadedByAndStatus(Long uploadedBy, FileStatus status, Pageable pageable);
    Page<File> findByTicketIdAndStatus(Long ticketId, FileStatus status, Pageable pageable);
    
    // 按文件名查询
    Optional<File> findByFileName(String fileName);
    Optional<File> findByFileHash(String fileHash);
    
    // 统计方法
    long countByFileType(FileType fileType);
    long countByStatus(FileStatus status);
    long countByUploadedBy(Long uploadedBy);
    long countByTicketId(Long ticketId);
    long countByCreatedAtAfter(Instant instant);
    long countByCreatedAtBetween(Instant start, Instant end);
    
    // 按时间范围查询
    @Query("SELECT f FROM File f WHERE f.createdAt >= :start AND f.createdAt <= :end ORDER BY f.createdAt DESC")
    List<File> findByCreatedAtBetween(@Param("start") Instant start, @Param("end") Instant end);
    
    // 搜索文件
    @Query("SELECT f FROM File f WHERE " +
           "(:keyword IS NULL OR " +
           "f.fileName LIKE %:keyword% OR " +
           "f.originalFileName LIKE %:keyword% OR " +
           "f.description LIKE %:keyword% OR " +
           "f.tags LIKE %:keyword%) " +
           "AND (:fileType IS NULL OR f.fileType = :fileType) " +
           "AND (:status IS NULL OR f.status = :status) " +
           "AND (:uploadedBy IS NULL OR f.uploadedBy = :uploadedBy) " +
           "AND (:ticketId IS NULL OR f.ticketId = :ticketId) " +
           "ORDER BY f.updatedAt DESC")
    Page<File> searchFiles(@Param("keyword") String keyword,
                          @Param("fileType") FileType fileType,
                          @Param("status") FileStatus status,
                          @Param("uploadedBy") Long uploadedBy,
                          @Param("ticketId") Long ticketId,
                          Pageable pageable);
    
    // 获取文件统计
    @Query("SELECT f.fileType, COUNT(f) FROM File f WHERE f.status = 'ACTIVE' GROUP BY f.fileType")
    List<Object[]> getFileTypeDistribution();
    
    @Query("SELECT f.status, COUNT(f) FROM File f GROUP BY f.status")
    List<Object[]> getFileStatusDistribution();
    
    // 获取文件大小统计
    @Query("SELECT SUM(f.fileSize) FROM File f WHERE f.status = 'ACTIVE'")
    Long getTotalFileSize();
    
    @Query("SELECT f.fileType, SUM(f.fileSize) FROM File f WHERE f.status = 'ACTIVE' GROUP BY f.fileType")
    List<Object[]> getFileSizeByType();
    
    // 获取下载统计
    @Query("SELECT SUM(f.downloadCount) FROM File f WHERE f.status = 'ACTIVE'")
    Long getTotalDownloads();
    
    // 获取最近上传的文件
    @Query("SELECT f FROM File f WHERE f.status = 'ACTIVE' ORDER BY f.createdAt DESC")
    List<File> findRecentFiles(Pageable pageable);
    
    // 获取热门文件（下载次数最多）
    @Query("SELECT f FROM File f WHERE f.status = 'ACTIVE' ORDER BY f.downloadCount DESC")
    List<File> findPopularFiles(Pageable pageable);
    
    // 获取大文件
    @Query("SELECT f FROM File f WHERE f.fileSize > :minSize AND f.status = 'ACTIVE' ORDER BY f.fileSize DESC")
    List<File> findLargeFiles(@Param("minSize") Long minSize, Pageable pageable);
    
    // 获取用户文件统计
    @Query("SELECT f.fileType, COUNT(f) FROM File f WHERE f.uploadedBy = :userId AND f.status = 'ACTIVE' GROUP BY f.fileType")
    List<Object[]> getUserFileStats(@Param("userId") Long userId);
    
    // 获取工单文件统计
    @Query("SELECT f.fileType, COUNT(f) FROM File f WHERE f.ticketId = :ticketId AND f.status = 'ACTIVE' GROUP BY f.fileType")
    List<Object[]> getTicketFileStats(@Param("ticketId") Long ticketId);
    
    // 清理过期文件
    @Query("SELECT f FROM File f WHERE f.status = 'DELETED' AND f.updatedAt < :cutoffTime")
    List<File> findExpiredDeletedFiles(@Param("cutoffTime") Instant cutoffTime);
    
    // 获取文件趋势数据
    @Query("SELECT DATE(f.createdAt) as date, COUNT(f) as count FROM File f " +
           "WHERE f.createdAt >= :startDate AND f.createdAt <= :endDate " +
           "GROUP BY DATE(f.createdAt) ORDER BY date ASC")
    List<Object[]> getFileTrendData(@Param("startDate") Instant startDate, @Param("endDate") Instant endDate);
}
